Анализ бизнес-показателей Яндекс.Афиши

Описание проекта

Вас пригласили на стажировку в отдел аналитики Яндекс.Афиши. Первое задание: помочь маркетологам оптимизировать маркетинговые затраты.

У нас в распоряжении есть данные от Яндекс.Афиши с июня 2017 по конец мая 2018 года:

Мы изучим:

Описание данных

Данные представлены в файлах

Таблица visits (лог сервера с информацией о посещениях сайта):

Таблица orders (информация о заказах):

Таблица costs (информация о затратах на маркетинг):

Оглавление

1. Загрузка и подготовка данных к анализу

Загрузите данные о визитах, заказах и расходах в переменные. Оптимизируйте данные для анализа. Убедитесь, что тип данных в каждой колонке — правильный.

В таблице costs нет пропусков или дубликатов. остается только преобразовать параметр dt в формат даты-времени.

Таблица costs готова к анализу.

В таблице orders_log нет пропусков или дубликатов. остается только преобразовать параметр Buy Ts в формат даты-времени и переименовать колонки по стандарту.

Таблица orders_log готова к анализу.

В таблице visits_log нет пропусков или дубликатов. остается только преобразовать параметры даты-времени в нужный формат и переименовать колонки по стандарту.

Все таблицы готовы к анализу.

Вывод раздела 1

Мы прочитали исходные данные, проверили пропуски и дубликаты, преобразовали параметры даты-времени в соответствующие форматы и переименовали колонки по стандарту.

2. Подготовка отчетов и метрик

Подготовим отчет по продукту

Для расчета сессий пользователей воспользуемся таблицей visits с информацией о визитах.

Посчитаем длительность сессий с точностью до минут.

Посмотрим на распределение длительностей сессий по гистограмме.

Большинство сессий очень короткие, но есть и длинные. Наибольшее число сессий с длительностью 1 минута. По используемым устройствам картина различается:

Для сессий со смартфонов характерны быстрые сессии.

Проверим, сколько есть нулевых сессий.

10% данных занимают очень короткие сессии, скорее всего вызванные техническими ошибками при подключении. Для анализа потребуются данные по "настоящим" сессиям не меньше 1 минуты.

Количество уникальных пользователей

Посчитаем сколько сессий и уникальных пользователей есть в месяц, неделю, день.

Посчитаем итоговые метрики DAU, WAU, MAU по числу уникальных пользователей в день, неделю, месяц.

Посчитаем метрику "липкий фактор" по отношению к недельному и месячному показателям.

Вовлеченнность пользователей (sticky factor) по неделе составляет 16%, что совсем немногим лучше 1/7 = 14%, как если бы каждый день были новые ползователи. Такая же ситуация и по месяцу: 3.9% немногим выше 1/30 = 3.3%. Можно заключить, что аудитория портала постоянно сменяется, только очень малая доля пользователей возвращается.

Проверим эти же показатели по платформам.

Вовлеченнность пользователей по платформам немного отличается, для настольных систем по недельному показателю 16.0%, для мобильных 15.4%. По месячному показателю 3.9% против 3.7% соотвественно. Вовлеченность пользователей с настольных систем выше, чем с мобильных устройств. По-видимому, пользователи настольных систем чаще добавляют сайт в закладки или используют автозаполнение в браузере и таким образом чаще возвращаются на уже посещенные сайты.

Расчитаем также среднее количество сессий в день.

Как количество уникальных пользователей, так и количество сессий в день больше на настольных системах. Вовлеченность пользователей также выше на компьютерах.

Оценка количества возвращающихся пользователей

Посчитаем Retention Rate (коэффициент удержания) и Churn Rate (коэффициент оттока).

Проведем когортный анализ по времени первого визита, используя данные из таблицы sessions.

Выделим даты первого визита для всех пользователей.

Совместим получившуюся таблицу с изначальной, чтобы создать общий датафрейм.

В данных собрана информация по визитам за целый год с июня 2017 по июнь 2018, поэтому логично проводить когортный анализ по месяцам первой покупки.

Вычислим отдельно день и месяц первой покупки.

Расчитаем возраст когорты по разнице между месяцем визита и месяцем первого визита.

Расчитаем сколько визитов совершают пользователи в каждой когорте.

Если исключить первый столбец, различия в следующие периоды будут более явные.

Как видно, очень мало пользователей возвращаются на портал по всем источникам. Больше всего пользователей осталось из самой первой когорты - 3.2%.

Посмотрим по устройствам.

Как видно, после первого месяца остается на настольных системах всего 6% пользователей, для смартфонов еще меньше - 4%. В сентябре 2019 года для платформ заметно, что осталось больше пользователей во второй месяц (для смартфонов) и второй-третий месяц (для настольных систем). Возможно, это связано с маркетиновыми акциями или другими факторами, которые пришлись на этот период.

Рассчитаем коэффициент оттока клиентов.

Расчеты метрик оттока клиентов показывают, что после первого месяца на обеих платформах остается всего около 5-7% пользователей, а их число потом также спадает. В последнем периоде отток клиентов наибольший (от 40% до 60%). Для первой когорты характерен рост числа пользователей на третий месяц в размере 17%-19%, что соответствует сентябрю 2017 года, где мы ранее отмечали меньший отток клиентов. Возможно, маркетинговые акции не только способствовали удержанию новых клиентов, но и вернули тех, кто ранее уже пользовался продуктом. Также можно отметить, что более новые когорты показывают даже больший отток, чем более старые, для них наблюдается сильный отток уже со второго-третьего месяца. Для последней когорты в анализе отток пользователей уже на второй месяц составил внушительные 98% для всех платформ, что сильно хуже первой когорты с показателем 94%-95%.

Расчет метрик продаж

Для ответа на вопрос "Когда люди начинают покупать?" выделим из таблицы с сесииями для каждого пользователя дату визита, а затем совметим ее с таблицей заказов по uid.

Совместим таблицу визитов с заказами по пользователям, исходя из предположения, что заказ сопровождается визитом и их даты совпадают. Таким образом мы сможем получить данные с какого устройства и из какого источника пришел пользователь в первый раз и когда совершил покупку.

Колонка с временем заказа не нужна.

Теперь в одной таблице собрана информация о том, когда были визиты и был ли заказ при этом визите.

Проверим на конкретном пользователе.

Так как нельзя понять по предоставленным данным, откуда пришел пользователь, если он в течение дня заходил из 2 источников (3 и 5), в таблице заказ был отмечен для каждого источника.

Теперь сгруппируем данные по пользователю, устройству и источнику по времени первого заказа и первой покупке.

Таким образом в получившейся сводной таблице для каждого источника и устройства есть данные о первом заказе и первом визите. Расчитаем задержку между визитом и заказом.

Как видно по данным, подавляющее большинство заказов совершается в день первого визита, посмотроим еще одну сводную таблицу, чтобы узнать как это различается для разных источников трафика.

В 90% случаев заказ происходит в тот же день. По сводной таблице выше видно, что по 6 источнику не было совершено ни одного заказа, а по 7 источнику заказ был, но только с настольной платформы. Несмотря на то, что большинство заказов по медиане совершались в тот же день, по среднему можно сделать вывод, что быстрее всего с настольных систем в среднем совершали заказ на 2-3 день пользователи из источника 5, далее источники 1, 10, 3, 4 и 2. В среднем заказс мобильных устройств совершался на 2-3 день для всех источников. Также видно, что покупки по времени для источника 1 практически равны для настольных и мобильных систем, поэтому источник 1 можно считать универсальным по платформам.

Проведем когортный анализ по времени первой покупки, используя данные из таблицы orders_log.

Выделим даты первой покупки для всех пользователей.

Совместим получившуюся таблицу с изначальной, чтобы создать общий датафрейм.

В данных собрана информация по заказам за целый год с июня 2017 по июнь 2018, поэтому логично проводить когортный анализ по месяцам первой покупки.

Вычислим отдельно день и месяц первой покупки.

Расчитаем время возраст когорты по разнице между месяцем заказа и месяцем первого заказа.

Расчитаем сколько покупок совершают пользователи по месяцам с помощью когортного анализа.

Больше всего заказов было совершено в декабре 2017 года, а также в октябре и ноябре. Меньше всего заказов совершили в августе. Количество заказов по когортам уменьшалось в следующие периоды и было на порядок меньше, чем в первый период.

Расчитаем валовые продажи по когортам.

Посчитаем сколько покупателей в каждой когорте.

Посчитаем выручку в каждый месяц для каждой когорты.

Объединим это с данными по количеству покупателей в период.

Примем, что маржинальной равна 100% и вся выручка равняется прибыли компании.

Посчитаем средний чек по периодам, разделив прибыль на количество продаж.

Расчитаем число покупок по когортам и период.

Рассчитаем отношение количество заказов к количеству покупателей за период.

Как видно по графику выше количество заказов на одного покупателя падает, несмотря на то, что общее число заказов росло. Это может быть вызвано тем, что маркетинговые акции привлекали больше уникальных новых покупателей, но не возвращали старых. Таким образом, на текущий момент количество заказов практически равно количеству привлеченных новых пользователей.

Наибольший средний чек в размере 53.2 наблюдался для когорты сентября 2017 года в ноябре на третий месяц жизни когорты. Также для когорты клиентов, которые впервые делали заказ в декабре 2017 года, видно, что максимальный средний чек для них был в феврале, марте и апреле 2018 года, что, возможно, связано с праздниками в это время.

Расчитаем LTV Lifetime Value для клиента. Для этого валовую прибыль нужно разделить на количество покупателей.

Для лучшей визуализации посчитаем накопительную сумму LTV по времени жизни когорт.

Как видно на визуализации с накопительным LTV когорта сентября 2017 уже на второй месяц принесла больше дохода, чем когорта июня 2017 после 4 месяцев, а на третий месяц принесла больше, чем на 7 месяц первый когорты. На втором месте по скорости принесения прибыли когорта июля 2017 года, которая уже на втором периоде принесла больше, чем за 3 периода когорта июня 2017 года.

Как видно по графику, наибольшую прибыль принесла когорта пользователей сентября 2017 года (LTV = 13.4), далее идет первая когорта (которая также может включать все предыдущие периоды) (LTV = 11.9). С течением времени LTV уменьшается.

Если сравнить отдельно когорты, которые прожили как минимум 6 периодов, то среди них также лучшей окажется когорта сентября, затем когорта июня, июля и августа. Когорты октября и ноября показали практически в 2 раза меньший результат, чем когорта сентября за тот же срок.

Анализ затрат на маркетинг

Проанализируем таблицу с расходами.

Посчитаем общие затраты.

Всего затрат за год на 329131.62.

Рассчитаем траты в месяц по разным источникам.

Больше всего затрат было потрачено на источник 3 в размере 141321.63. Затраты по времени были максимальны в ноябре 2017 года и минимальны в августе 2017 года. Судя по графику выше расходы нарастали с сентября 2017 года и убывали, начиная с декабря 2017 года вплоть до конца выборки.

Рассчитаем расходы на привлечение одного клиента. Для этого совместим подготовленный датафрейм с количеством покупателей в каждой когорте

Определим откуда пришли пользователи-посетители в первый раз.

Соединим первые покупки и источники.

Получили таблицу с распределением первых заказов по источникам для каждого клиента.

Для расчета LTV добавим заказы к таблице с первыми заказами.

Расчитаем время жизни когорты.

Для расчета количества заказов по источникам составим сводную таблицу.

Больше всего заказов было размещено после источника трафика 3, затем 4 и 5. Продаж через источники 6,7,8 практически не было. На второй месяц продажи уменьшались практически на порядок для всех источников.

Теперь рассчитаем количество покупателей по источникам и времени жизни когорты.

Видно, что количество клиентов с заказами повторяет распределение количества заказов по источникам.

Для расчета LTV сначала расчитаем доход по источникам и времени жизни когорт.

Для получения LTV нужно получить кумулятивную сумму по строкам.

Как видно по тепловой карте больше всего выручки принес источник 4, затем 3, 5. Источники 9 и 10 сильно отстают от других источников по прибыли. Видно, что между 2 и 3 периодом (август-сентябрь 2017) произошло резкое увеличение выручки по источнику 5 (на 10 тыс.). Это, по-видимому, связано с увеличением бюджета на этот источник и привлечением новых пользователей через этот канал.

Далее для расчета LTV получим количество покупателей в каждый период по источникам.

Получившееся количество покупателей добавим к таблице для расчета LTV.

Теперь можно разделить кумулятивную сумму на количество покупателей по-элементно.

По распределению видно, что наибольший LTV у источника 2, затем 1 и 5 на конец выборки. В то же время, скорость изменения LTV различна по каналам. Так, например, источники 1 и 2 быстрее других наращивали LTV, а на 3 период (сентябрь 2017) источник 5 резко увеличил LTV.

Посчитаем CAC из таблицы с затратами по источникам.

Совместим эту таблицу с количеством покупателей по источникам.

Сделаем индексом source_id по примеру других таблиц.

Наконец все готово для расчета ROMI.

Теперь разделим таблицу с LTV на CAC, чтобы получить ROMI. Последний столбец также удалим.

Как показал анализ, на конец выборки окупились источники 1, 2, 5 и 9. Причем лидером является источник 1, он же окупился уже после 1 периода. Расходным источником с ROMI = 0.4 остался номер 3. Причем источник 4, несмотря на то, что не вышел на показатель 1, показывает устойчивый рост со временем. Источник 10 имеет ROMI = 0.7, но никак не изменялся по времени, что может быть связано с тем, что бюджета на этот источник практически не было.

Вывод раздела 2

В этом разделе были проведены расчеты различных метрик с построены графики для визуализации.

1. Подготовили отчет по продукту

2. Рассчитали метрики продаж

3. Провели анализ затрат на маркетинг

3. Общий вывод и рекомендации

По результатам анализа на текущий момент затраты на маркетинг не окупаются. В то же время, благодаря запуску типа источника покупателей 3 в августе 2017 года количество покупателей выросло практически в 3 раза, что сразу дало показатель окупаемости свыше 1 в сентябре. В то же время, дальнейшие затраты на этот источник трафика не принес результата в связи с довольно низким средним чеком, что не дает окупиться затратам на привлечение клиентов. Было бы логично испробовать другие источники трафика и увеличить их финансирование за счет источника 3.

Так как больше всего пользователей приходят с настольных систем и только малая часть со смартфонов, стоит дополнительно рассмотреть маркетинговые акции и способы привлечения, нацеленные на пользователей смартфонов, так как, возможно, еще не исчерпан предел пользователем, которым был бы интересен сервис с мобильных устройств. В то же время, среднее время сессии на мобильных устройствах ниже и количество визитов также ниже, чем для настольных систем.

С учетом показателей метрики LTV валовой прибыли с клиента и низкой доли возвращающихся клиентов стоит рассмотреть дополнительные маркетинговый акции, направленные на удержание клиента (например, бонусная программа). Как показывает анализ затраты на привлечение пользовательских когорт могут окупаться спустя несколько месяцев, поэтому очень важно привлекать клиентов, которые уже попробовали продукт.

Как показал анализ, на конец выборки окупились источники 1, 2, 5 и 9. С точки зрения использования маркетингового бюджета стоит сохранить канал привлечения 1, но сократить расходы на каналы 3 и 4, так как их привлечение не окупается, а больше всего средств расходуется на канал 3. 4 канал в долгосрочной перспективе смог бы окупится, но он требует слишком большого бюджета, а ROMI растет слишком медленно. Каналы 6, 7, 8 практически не принесли новых заказов, но и бюджет на них был минимален. Стоит внимательнее отнестись к этим источникам, так как они могут как являться точкой потенциального роста, так и просто привлекать не ту аудиторию и развивать их не имеет смысла. Источник 10 показал стабильный результат, но за счет того, что бюджет на него не изменялся, возможно не смог раскрыть весь свой потенциал.